import sqlite3
import json
import time
from typing import Optional, List, Dict, Any, Tuple
from aiohttp import web
from pydantic import BaseModel, validator, Field, RootModel  # 新增导入RootModel
from ..core.database.connection import db_manager


# 数据模型验证
class TagQueryParams(BaseModel):
    name: Optional[str] = None
    group_name: Optional[str] = None
    tag: Optional[str] = None
    tag_des: Optional[str] = None
    limit: int = Field(50, ge=1, le=50)


class TagsPagedQuery(BaseModel):
    group: Optional[str] = None
    name: Optional[str] = None
    tag: Optional[str] = None
    description: Optional[str] = None
    page: int = Field(1, ge=1)
    pageSize: int = Field(100, ge=1, le=100)


class TagCreate(BaseModel):
    group: str
    name: str
    tag: str
    description: str


class TagUpdate(BaseModel):
    groupId: int = Field(..., gt=0)
    group: str
    name: str
    oldTag: str
    newTag: Optional[str] = None
    description: Optional[str] = None


class TagDelete(BaseModel):
    groupId: int = Field(..., gt=0)
    value: str


class BatchRandomTagRequestItem(BaseModel):
    group: str
    name: Optional[str] = None
    size: int = Field(..., ge=0, le=100)


# 修改根模型定义，使用RootModel替代__root__字段
class BatchRandomTagRequest(RootModel):
    root: List[BatchRandomTagRequestItem]


class RandomConfigCreate(BaseModel):
    name: str
    conditions: List[Dict[str, Any]]

    @validator('conditions')
    def check_conditions(cls, v):
        for cond in v:
            if not isinstance(cond.get('path'), list):
                raise ValueError('条件中的path必须是数组')
            if not isinstance(cond.get('group'), str) or not cond.get('group'):
                raise ValueError('条件中的group必须是非空字符串')
            if not isinstance(cond.get('size'), int) or cond.get('size') < 1 or cond.get('size') > 100:
                raise ValueError('条件中的size必须是1-100之间的整数')
        return v


class RandomConfigUpdate(BaseModel):
    name: Optional[str] = None
    conditions: Optional[List[Dict[str, Any]]] = None


class SequentialTagsRequest(BaseModel):
    group: str
    name: Optional[str] = None
    size: int = Field(10000, ge=0, le=10000)


# 通用工具函数
def dict_from_row(row: sqlite3.Row) -> Dict[str, Any]:
    """将sqlite3.Row转换为字典"""
    if row is None:
        return None
    return {key: row[key] for key in row.keys()}


# 业务逻辑服务
class TagService:
    @staticmethod
    async def get_character_tags(
            name: Optional[str],
            group_name: Optional[str],
            tag: Optional[str],
            tag_des: Optional[str],
            limit: int = 50
    ) -> List[Dict[str, Any]]:
        """获取角色标签"""
        where: List[str] = []
        params: List[Any] = []

        if name:
            where.append('name = ?')
            params.append(name)
        if group_name:
            where.append('group_name = ?')
            params.append(group_name)

        fuzzy_conditions: List[str] = []
        if tag:
            fuzzy_conditions.append('name LIKE ?')
            params.append(f'%{tag}%')
        if tag_des:
            fuzzy_conditions.append('cn_name LIKE ?')
            params.append(f'%{tag_des}%')

        if fuzzy_conditions:
            where.append(f'({" OR ".join(fuzzy_conditions)})')

        where_clause = f'WHERE {" AND ".join(where)}' if where else ''
        query = f"""
            SELECT
                tag as value,
                cn_name as label,
                name
            FROM character {where_clause}
            LIMIT ?
        """
        params.append(limit)

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(query, params)
            return [dict_from_row(row) for row in cursor.fetchall()]

    @staticmethod
    async def get_tags(
            group: Optional[str],
            name: Optional[str],
            tag: Optional[str],
            description: Optional[str],
            page: int = 1,
            pageSize: int = 100
    ) -> Dict[str, Any]:
        """获取标签列表（分页）"""
        offset = (page - 1) * pageSize
        where: List[str] = []
        params: List[Any] = []

        # 严格匹配条件
        if group:
            where.append('tg.group_name = ?')
            params.append(group)
        if name:
            where.append('tg.name = ?')
            params.append(name)

        # 模糊匹配条件
        or_conditions: List[str] = []
        if tag:
            or_conditions.append('t.tag LIKE ?')
            params.append(f'%{tag}%')
        if description:
            or_conditions.append('t.tag_des LIKE ?')
            params.append(f'%{description}%')

        if or_conditions:
            where.append(f'({" OR ".join(or_conditions)})')

        # 基础查询
        base_sql = """
            SELECT
                t.tag_group_id AS "groupId",
                t.tag AS value,
                tg.group_name AS "group",
                tg.name,
                t.tag_des AS label
            FROM tags t
            JOIN tag_groups tg ON t.tag_group_id = tg.id
        """

        if where:
            base_sql += f' WHERE {" AND ".join(where)}'

        # 数据查询
        data_sql = f'{base_sql} LIMIT ? OFFSET ?'
        data_params = params + [pageSize, offset]

        # 总数查询
        count_sql = f'SELECT COUNT(*) AS total FROM ({base_sql})'

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row

            # 获取数据
            data_cursor = conn.execute(data_sql, data_params)
            data = [dict_from_row(row) for row in data_cursor.fetchall()]

            # 获取总数
            total_cursor = conn.execute(count_sql, params)
            total = total_cursor.fetchone()['total']

            return {
                "data": data,
                "pagination": {
                    "total": total,
                    "page": page,
                    "pageSize": pageSize,
                    "totalPages": (total + pageSize - 1) // pageSize
                }
            }

    @staticmethod
    async def add_tag(group: str, name: str, tag: str, description: str) -> Dict[str, Any]:
        """添加标签"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            try:
                # 开启事务
                conn.execute('BEGIN TRANSACTION')

                # 检查标签是否已存在
                exist_tag = conn.execute("""
                    SELECT 1
                    FROM tags t
                    JOIN tag_groups tg ON t.tag_group_id = tg.id
                    WHERE tg.group_name = ? AND tg.name = ? AND t.tag = ?
                """, [group, name, tag]).fetchone()

                if exist_tag:
                    raise ValueError('相同分类下已存在该标签')

                # 插入分组（忽略重复）
                conn.execute("""
                    INSERT OR IGNORE INTO tag_groups (group_name, name)
                    VALUES (?, ?)
                """, [group, name])

                # 获取分组ID
                group_result = conn.execute("""
                    SELECT id FROM tag_groups
                    WHERE group_name = ? AND name = ?
                """, [group, name]).fetchone()

                if not group_result:
                    raise ValueError('分类创建失败')

                tag_group_id = group_result['id']

                # 插入标签
                cursor = conn.execute("""
                    INSERT INTO tags (tag_group_id, tag, tag_des)
                    VALUES (?, ?, ?)
                """, [tag_group_id, tag, description])

                # 提交事务
                conn.execute('COMMIT')

                return {
                    "changes": cursor.rowcount,
                    "tagInfo": {"tagGroupId": tag_group_id, "tag": tag}
                }
            except sqlite3.IntegrityError as e:
                conn.execute('ROLLBACK')
                if 'UNIQUE constraint' in str(e):
                    raise ValueError('标签已存在')
                raise e
            except Exception as e:
                conn.execute('ROLLBACK')
                raise e

    @staticmethod
    async def get_categories() -> Dict[str, Any]:
        """获取分类"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute("""
                SELECT group_name AS "group", name
                FROM tag_groups
                ORDER BY group_name, name
            """)
            data = [dict_from_row(row) for row in cursor.fetchall()]
            return {"data": data}

    @staticmethod
    async def update_tag(
            old_tag_group_id: int,
            old_tag: str,
            new_group_name: str,
            new_category_name: str,
            new_tag: Optional[str],
            description: Optional[str]
    ) -> Dict[str, Any]:
        """更新标签"""
        # 处理新标签名默认值
        new_tag = new_tag or old_tag
        description = description or ''

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            try:
                # 开启事务
                conn.execute('BEGIN TRANSACTION')

                # 确认原标签存在
                original_tag = conn.execute("""
                    SELECT 1 FROM tags
                    WHERE tag_group_id = ? AND tag = ?
                """, [old_tag_group_id, old_tag]).fetchone()

                if not original_tag:
                    raise ValueError('原标签不存在，无法更新')

                # 删除原标签
                delete_result = conn.execute("""
                    DELETE FROM tags
                    WHERE tag_group_id = ? AND tag = ?
                """, [old_tag_group_id, old_tag])

                if delete_result.rowcount == 0:
                    raise ValueError('原标签删除失败，更新终止')

                # 插入新分组（忽略重复）
                conn.execute("""
                    INSERT OR IGNORE INTO tag_groups (group_name, name)
                    VALUES (?, ?)
                """, [new_group_name, new_category_name])

                # 获取新分组ID
                new_group_result = conn.execute("""
                    SELECT id FROM tag_groups
                    WHERE group_name = ? AND name = ?
                """, [new_group_name, new_category_name]).fetchone()

                if not new_group_result:
                    raise ValueError('新标签分组创建/查询失败，无法新增标签')

                new_tag_group_id = new_group_result['id']

                # 检查新标签是否已存在
                duplicate_tag = conn.execute("""
                    SELECT 1 FROM tags
                    WHERE tag_group_id = ? AND tag = ?
                """, [new_tag_group_id, new_tag]).fetchone()

                if duplicate_tag:
                    raise ValueError(
                        f'新标签【{new_tag}】在分组【{new_group_name}-{new_category_name}】下已存在，请更换标签名')

                # 插入新标签
                insert_result = conn.execute("""
                    INSERT INTO tags (tag_group_id, tag, tag_des)
                    VALUES (?, ?, ?)
                """, [new_tag_group_id, new_tag, description])

                if insert_result.rowcount == 0:
                    raise ValueError('新标签插入失败，更新终止')

                # 提交事务
                conn.execute('COMMIT')

                return {
                    "success": True,
                    "changes": {
                        "deleted": 1,
                        "inserted": 1
                    },
                    "oldTagInfo": {
                        "tagGroupId": old_tag_group_id,
                        "tag": old_tag
                    },
                    "newTagInfo": {
                        "tagGroupId": new_tag_group_id,
                        "group": new_group_name,
                        "category": new_category_name,
                        "tag": new_tag,
                        "description": description
                    }
                }
            except sqlite3.IntegrityError as e:
                conn.execute('ROLLBACK')
                if 'UNIQUE constraint' in str(e):
                    raise ValueError('新标签与现有标签重复，请修改标签名')
                raise e
            except Exception as e:
                conn.execute('ROLLBACK')
                raise e

    @staticmethod
    async def delete_tag(group_id: int, tag: str) -> Dict[str, Any]:
        """删除标签"""
        with db_manager.get_connection() as conn:
            try:
                # 检查标签是否存在
                tag_exists = conn.execute("""
                    SELECT 1 FROM tags
                    WHERE tag_group_id = ? AND tag = ?
                """, [group_id, tag]).fetchone()

                if not tag_exists:
                    raise web.HTTPNotFound(reason='标签不存在')

                # 执行删除
                result = conn.execute("""
                    DELETE FROM tags
                    WHERE tag_group_id = ? AND tag = ?
                """, [group_id, tag])

                return {
                    "success": True,
                    "deletedTag": {"groupId": group_id, "tag": tag}
                }
            except sqlite3.IntegrityError as e:
                if 'FOREIGN KEY constraint' in str(e):
                    raise web.HTTPConflict(reason='存在未处理的关联数据')
                raise e

    @staticmethod
    async def batch_random_tags(requests: List[Dict[str, Any]]) -> Dict[str, Any]:
        """批量获取随机标签"""
        results = []
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row

            for req_item in requests:
                try:
                    group = req_item['group']
                    name = req_item.get('name')
                    size = int(req_item['size'])

                    size = max(0, min(size, 100))
                    if size <= 0:
                        continue

                    where: List[str] = ['tg.group_name = ?']
                    params: List[Any] = [group]

                    if name and name.strip():
                        where.append('tg.name = ?')
                        params.append(name)

                    query = """
                        SELECT
                            t.tag_group_id,
                            t.tag as value,
                            t.tag_des as label
                        FROM tags t
                        JOIN tag_groups tg ON t.tag_group_id = tg.id
                        WHERE {}
                        ORDER BY RANDOM()
                        LIMIT ?
                    """.format(' AND '.join(where))

                    params.append(size)

                    cursor = conn.execute(query, params)
                    records = [dict_from_row(row) for row in cursor.fetchall()]

                    results.append({
                        "group": group,
                        "name": name or None,
                        "size": size,
                        "tags": records
                    })
                except Exception:
                    continue

        return {"results": [r for r in results if r is not None]}

    @staticmethod
    async def get_random_configs() -> Dict[str, Any]:
        """获取所有随机配置"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute("""
                SELECT id, name, config_json, create_time
                FROM random_tag_configs
                ORDER BY create_time DESC
            """)

            configs = []
            for row in cursor.fetchall():
                try:
                    configs.append({
                        "id": row['id'],
                        "name": row['name'],
                        "createTime": row['create_time'],
                        "conditions": json.loads(row['config_json'])['conditions']
                    })
                except:
                    continue

            return {"data": configs}

    @staticmethod
    async def save_random_config(name: str, conditions: List[Dict[str, Any]]) -> Dict[str, Any]:
        """保存随机配置"""
        with db_manager.get_connection() as conn:
            try:
                # 检查名称是否已存在
                existing = conn.execute("""
                    SELECT id FROM random_tag_configs WHERE name = ?
                """, [name]).fetchone()

                if existing:
                    raise web.HTTPConflict(reason='配置名称已存在')

                create_time = int(time.time() * 1000)  # 毫秒时间戳
                config_json = json.dumps({"conditions": conditions})

                cursor = conn.execute("""
                    INSERT INTO random_tag_configs (name, config_json, create_time)
                    VALUES (?, ?, ?)
                """, [name, config_json, create_time])

                return {
                    "success": True,
                    "id": cursor.lastrowid,
                    "name": name,
                    "create_time": create_time
                }
            except Exception as e:
                raise e

    @staticmethod
    async def update_random_config(
            config_id: int,
            name: Optional[str] = None,
            conditions: Optional[List[Dict[str, Any]]] = None
    ) -> Dict[str, Any]:
        """更新随机配置"""
        with db_manager.get_connection() as conn:
            # 检查配置是否存在
            existing = conn.execute("""
                SELECT id FROM random_tag_configs WHERE id = ?
            """, [config_id]).fetchone()

            if not existing:
                raise web.HTTPNotFound(reason='配置不存在')

            updates: List[str] = []
            params: List[Any] = []

            if name:
                # 检查名称是否与其他配置冲突
                name_conflict = conn.execute("""
                    SELECT id FROM random_tag_configs
                    WHERE name = ? AND id != ?
                """, [name, config_id]).fetchone()

                if name_conflict:
                    raise web.HTTPConflict(reason='配置名称已存在')

                updates.append('name = ?')
                params.append(name)

            if conditions is not None:
                updates.append('config_json = ?')
                params.append(json.dumps({"conditions": conditions}))

            if not updates:
                raise web.HTTPBadRequest(reason='未提供更新数据')

            params.append(config_id)

            cursor = conn.execute(f"""
                UPDATE random_tag_configs
                SET {', '.join(updates)}
                WHERE id = ?
            """, params)

            if cursor.rowcount == 0:
                raise web.HTTPNotFound(reason='配置更新失败')

            return {"success": True, "id": config_id}

    @staticmethod
    async def delete_random_config(config_id: int) -> Dict[str, Any]:
        """删除随机配置"""
        with db_manager.get_connection() as conn:
            cursor = conn.execute("""
                DELETE FROM random_tag_configs WHERE id = ?
            """, [config_id])

            if cursor.rowcount == 0:
                raise web.HTTPNotFound(reason='配置不存在')

            return {"success": True, "id": config_id}

    @staticmethod
    async def get_random_config(config_id: int) -> Dict[str, Any]:
        """获取单个随机配置"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            config = conn.execute("""
                SELECT id, name, config_json, create_time
                FROM random_tag_configs
                WHERE id = ?
            """, [config_id]).fetchone()

            if not config:
                raise web.HTTPNotFound(reason='配置不存在')

            try:
                return {
                    "id": config['id'],
                    "name": config['name'],
                    "createTime": config['create_time'],
                    "conditions": json.loads(config['config_json'])['conditions']
                }
            except:
                raise web.HTTPInternalServerError(reason='配置解析失败')

    @staticmethod
    async def get_sequential_tags(
            group: str,
            name: Optional[str] = None,
            size: int = 10000
    ) -> Dict[str, Any]:
        """获取顺序标签"""
        where: List[str] = ['tg.group_name = ?']
        params: List[Any] = [group]

        if name and name.strip():
            where.append('tg.name = ?')
            params.append(name)

        query = """
            SELECT
                t.tag_group_id,
                t.tag as value,
                t.tag_des as label
            FROM tags t
            JOIN tag_groups tg ON t.tag_group_id = tg.id
            WHERE {}
        """.format(' AND '.join(where))

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(query, params)
            records = [dict_from_row(row) for row in cursor.fetchall()]

            return {
                "results": [{
                    "group": group,
                    "name": name or None,
                    "size": len(records),
                    "tags": records
                }]
            }


# 路由处理函数
async def handle_get_character_tags(request: web.Request) -> web.Response:
    try:
        # 解析查询参数
        params = TagQueryParams(** request.query)

        result = await TagService.get_character_tags(
            name=params.name,
            group_name=params.group_name,
            tag=params.tag,
            tag_des=params.tag_des,
            limit=params.limit
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_tags(request: web.Request) -> web.Response:
    try:
        # 解析查询参数
        params = TagsPagedQuery(**request.query)

        result = await TagService.get_tags(
            group=params.group,
            name=params.name,
            tag=params.tag,
            description=params.description,
            page=params.page,
            pageSize=params.pageSize
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_add_tag(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        tag_data = TagCreate(** data)

        result = await TagService.add_tag(
            group=tag_data.group,
            name=tag_data.name,
            tag=tag_data.tag,
            description=tag_data.description
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_categories(request: web.Request) -> web.Response:
    try:
        result = await TagService.get_categories()
        return web.json_response(result)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_update_tag(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        update_data = TagUpdate(**data)

        result = await TagService.update_tag(
            old_tag_group_id=update_data.groupId,
            old_tag=update_data.oldTag,
            new_group_name=update_data.group,
            new_category_name=update_data.name,
            new_tag=update_data.newTag,
            description=update_data.description
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_tag(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        delete_data = TagDelete(** data)

        result = await TagService.delete_tag(
            group_id=delete_data.groupId,
            tag=delete_data.value
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_batch_random_tags(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        # 相应修改：使用root属性访问数据
        batch_request = BatchRandomTagRequest(root=data)

        result = await TagService.batch_random_tags(
            requests=[item.dict() for item in batch_request.root]
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_random_configs(request: web.Request) -> web.Response:
    try:
        result = await TagService.get_random_configs()
        return web.json_response(result)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_save_random_config(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        config_data = RandomConfigCreate(**data)

        result = await TagService.save_random_config(
            name=config_data.name,
            conditions=config_data.conditions
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_update_random_config(request: web.Request) -> web.Response:
    try:
        config_id = int(request.match_info['id'])
        data = await request.json()
        config_data = RandomConfigUpdate(** data)

        result = await TagService.update_random_config(
            config_id=config_id,
            name=config_data.name,
            conditions=config_data.conditions
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_random_config(request: web.Request) -> web.Response:
    try:
        config_id = int(request.match_info['id'])

        result = await TagService.delete_random_config(
            config_id=config_id
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_random_config(request: web.Request) -> web.Response:
    try:
        config_id = int(request.match_info['id'])

        result = await TagService.get_random_config(
            config_id=config_id
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_sequential_tags(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        seq_data = SequentialTagsRequest(** data)

        result = await TagService.get_sequential_tags(
            group=seq_data.group,
            name=seq_data.name,
            size=seq_data.size
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_tags_by_filter(request: web.Request) -> web.Response:
    """按筛选条件删除所有符合条件的标签记录"""
    try:
        # 解析查询参数（与get_tags相同）
        group = request.query.get('group')
        name = request.query.get('name')
        tag = request.query.get('tag')
        description = request.query.get('description')

        # 构建查询条件
        where: List[str] = []
        params: List[Any] = []

        # 严格匹配条件
        if group:
            where.append('tg.group_name = ?')
            params.append(group)
        if name:
            where.append('tg.name = ?')
            params.append(name)

        # 模糊匹配条件
        or_conditions: List[str] = []
        if tag:
            or_conditions.append('t.tag LIKE ?')
            params.append(f'%{tag}%')
        if description:
            or_conditions.append('t.tag_des LIKE ?')
            params.append(f'%{description}%')

        if or_conditions:
            where.append(f'({" OR ".join(or_conditions)})')

        # 如果没有任何筛选条件，拒绝删除所有记录
        if not where:
            return web.json_response({
                "error": "删除操作必须提供至少一个筛选条件，以防止误删除所有记录"
            }, status=400)

        where_clause = f'WHERE {" AND ".join(where)}' if where else ''

        # 执行删除操作
        with db_manager.get_connection() as conn:
            # 先查询符合条件的记录数量
            count_sql = f"""
                SELECT COUNT(*) AS total
                FROM tags t
                JOIN tag_groups tg ON t.tag_group_id = tg.id
                {where_clause}
            """
            count_cursor = conn.execute(count_sql, params)
            total = count_cursor.fetchone()[0]

            if total == 0:
                return web.json_response({
                    "success": True,
                    "message": "未找到符合条件的记录",
                    "deleted_count": 0
                })

            # 执行删除
            delete_sql = f"""
                DELETE FROM tags
                WHERE EXISTS (
                    SELECT 1 FROM tag_groups tg
                    WHERE t.tag_group_id = tg.id
                    {where_clause}
                )
            """
            delete_cursor = conn.execute(delete_sql, params)
            conn.commit()

            return web.json_response({
                "success": True,
                "message": f"成功删除 {delete_cursor.rowcount} 条记录",
                "deleted_count": delete_cursor.rowcount
            })

    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


# 路由配置
routes = [
    # 获取角色标签
    web.get("/sql/get_character_tags", handle_get_character_tags),
    # 获取标签列表
    web.get("/sql/get_tags", handle_get_tags),
    # 添加标签
    web.post("/sql/add_tag", handle_add_tag),
    # 获取分类
    web.get("/sql/get_categories", handle_get_categories),
    # 更新标签
    web.post("/sql/update_tag", handle_update_tag),
    # 删除标签
    web.post("/sql/delete_tag", handle_delete_tag),

    web.delete("/sql/delete_tags_by_filter", handle_delete_tags_by_filter),
    # 批量获取随机标签
    web.post("/sql/batch_random_tags", handle_batch_random_tags),
    # 获取所有随机配置
    web.get("/sql/get_random_configs", handle_get_random_configs),
    # 保存随机配置
    web.post("/sql/save_random_config", handle_save_random_config),
    # 更新随机配置
    web.put("/sql/update_random_config/{id}", handle_update_random_config),
    # 删除随机配置
    web.delete("/sql/delete_random_config/{id}", handle_delete_random_config),
    # 获取单个随机配置
    web.get("/sql/get_random_config/{id}", handle_get_random_config),
    # 获取顺序标签
    web.post("/sql/sequential_tags", handle_sequential_tags),
]
